/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to you under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.calcite.test;
import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.DateTimeUtils;
import org.apache.calcite.runtime.CalciteException;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.runtime.Utilities;

import com.google.common.collect.ImmutableList;

import org.hamcrest.Matcher;
import org.junit.jupiter.api.Test;

import java.math.BigDecimal;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import static org.apache.calcite.avatica.util.DateTimeUtils.MILLIS_PER_DAY;
import static org.apache.calcite.avatica.util.DateTimeUtils.dateStringToUnixDate;
import static org.apache.calcite.avatica.util.DateTimeUtils.timeStringToUnixDate;
import static org.apache.calcite.avatica.util.DateTimeUtils.timestampStringToUnixDate;
import static org.apache.calcite.runtime.SqlFunctions.arraysOverlap;
import static org.apache.calcite.runtime.SqlFunctions.charLength;
import static org.apache.calcite.runtime.SqlFunctions.concat;
import static org.apache.calcite.runtime.SqlFunctions.concatMulti;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiObjectWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiTypeWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithNull;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatWithNull;
import static org.apache.calcite.runtime.SqlFunctions.convertOracle;
import static org.apache.calcite.runtime.SqlFunctions.fromBase64;
import static org.apache.calcite.runtime.SqlFunctions.greater;
import static org.apache.calcite.runtime.SqlFunctions.initcap;
import static org.apache.calcite.runtime.SqlFunctions.internalToDate;
import static org.apache.calcite.runtime.SqlFunctions.internalToTime;
import static org.apache.calcite.runtime.SqlFunctions.internalToTimestamp;
import static org.apache.calcite.runtime.SqlFunctions.lesser;
import static org.apache.calcite.runtime.SqlFunctions.lower;
import static org.apache.calcite.runtime.SqlFunctions.ltrim;
import static org.apache.calcite.runtime.SqlFunctions.md5;
import static org.apache.calcite.runtime.SqlFunctions.overlay;
import static org.apache.calcite.runtime.SqlFunctions.position;
import static org.apache.calcite.runtime.SqlFunctions.replace;
import static org.apache.calcite.runtime.SqlFunctions.rtrim;
import static org.apache.calcite.runtime.SqlFunctions.sha1;
import static org.apache.calcite.runtime.SqlFunctions.sha256;
import static org.apache.calcite.runtime.SqlFunctions.sha512;
import static org.apache.calcite.runtime.SqlFunctions.substring;
import static org.apache.calcite.runtime.SqlFunctions.toBase64;
import static org.apache.calcite.runtime.SqlFunctions.toInt;
import static org.apache.calcite.runtime.SqlFunctions.toIntOptional;
import static org.apache.calcite.runtime.SqlFunctions.toLong;
import static org.apache.calcite.runtime.SqlFunctions.toLongOptional;
import static org.apache.calcite.runtime.SqlFunctions.toTimestampWithLocalTimeZone;
import static org.apache.calcite.runtime.SqlFunctions.trim;
import static org.apache.calcite.runtime.SqlFunctions.upper;
import static org.apache.calcite.test.Matchers.isListOf;

import static org.hamcrest.CoreMatchers.containsString;
import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.nullValue;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.closeTo;
import static org.hamcrest.Matchers.hasToString;
import static org.junit.jupiter.api.Assertions.assertArrayEquals;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertSame;
import static org.junit.jupiter.api.Assertions.fail;

import static java.nio.charset.StandardCharsets.UTF_8;

/**
 * Unit test for the methods in {@link SqlFunctions} that implement SQL
 * functions.
 *
 * <p>Developers, please use {@link org.hamcrest.MatcherAssert#assertThat assertThat}
 * rather than {@code assertEquals}.
 */
class SqlFunctionsTest {
  static <E> List<E> list(E... es) {
    return Arrays.asList(es);
  }

  static <E> List<E> list() {
    return ImmutableList.of();
  }

  @Test void testArraysOverlap() {
    final List<Object> listWithOnlyNull = new ArrayList<>();
    listWithOnlyNull.add(null);

    // list2 is empty
    assertThat(arraysOverlap(list(), list()), is(false));
    assertThat(arraysOverlap(listWithOnlyNull, list()), is(false));
    assertThat(arraysOverlap(list(1, null), list()), is(false));
    assertThat(arraysOverlap(list(1, 2), list()), is(false));

    // list2 contains only nulls
    assertThat(arraysOverlap(list(), listWithOnlyNull), is(false));
    assertThat(arraysOverlap(listWithOnlyNull, listWithOnlyNull), is(nullValue()));
    assertThat(arraysOverlap(list(1, null), listWithOnlyNull), is(nullValue()));
    assertThat(arraysOverlap(list(1, 2), listWithOnlyNull), is(nullValue()));

    // list2 contains a mixture of nulls and non-nulls
    assertThat(arraysOverlap(list(), list(1, null)), is(false));
    assertThat(arraysOverlap(listWithOnlyNull, list(1, null)), is(nullValue()));
    assertThat(arraysOverlap(list(1, null), list(1, null)), is(true));
    assertThat(arraysOverlap(list(1, 2), list(1, null)), is(true));

    // list2 contains only non-null
    assertThat(arraysOverlap(list(), list(1, 2)), is(false));
    assertThat(arraysOverlap(listWithOnlyNull, list(1, 2)), is(nullValue()));
    assertThat(arraysOverlap(list(1, null), list(1, 2)), is(true));
    assertThat(arraysOverlap(list(1, 2), list(1, 2)), is(true));
  }

  @Test void testCharLength() {
    assertThat(charLength("xyz"), is(3));
  }

  @Test void testToString() {
    assertThat(SqlFunctions.toString(0f), is("0E0"));
    assertThat(SqlFunctions.toString(1f), is("1.0"));
    assertThat(SqlFunctions.toString(1.5f), is("1.5"));
    assertThat(SqlFunctions.toString(-1.5f), is("-1.5"));
    assertThat(SqlFunctions.toString(1.5e8f), is("1.5E8"));
    assertThat(SqlFunctions.toString(-0.0625f), is("-0.0625"));
    assertThat(SqlFunctions.toString(0.0625f), is("0.0625"));
    assertThat(SqlFunctions.toString(-5e-12f), is("-5.0E-12"));

    assertThat(SqlFunctions.toString(0d), is("0E0"));
    assertThat(SqlFunctions.toString(1d), is("1.0"));
    assertThat(SqlFunctions.toString(1.5d), is("1.5"));
    assertThat(SqlFunctions.toString(-1.5d), is("-1.5"));
    assertThat(SqlFunctions.toString(1.5e8d), is("1.5E8"));
    assertThat(SqlFunctions.toString(-0.0625d), is("-0.0625"));
    assertThat(SqlFunctions.toString(0.0625d), is("0.0625"));
    assertThat(SqlFunctions.toString(-5e-12d), is("-5.0E-12"));

    assertThat(SqlFunctions.toString(new BigDecimal("0")), is("0"));
    assertThat(SqlFunctions.toString(new BigDecimal("1")), is("1"));
    assertThat(SqlFunctions.toString(new BigDecimal("1.5")), is("1.5"));
    assertThat(SqlFunctions.toString(new BigDecimal("-1.5")), is("-1.5"));
    assertThat(SqlFunctions.toString(new BigDecimal("1.5e8")), is("1.5E+8"));
    assertThat(SqlFunctions.toString(new BigDecimal("-0.0625")), is("-.0625"));
    assertThat(SqlFunctions.toString(new BigDecimal("0.0625")), is(".0625"));
    assertThat(SqlFunctions.toString(new BigDecimal("-5e-12")), is("-5E-12"));
  }

  @Test void testConcat() {
    assertThat(concat("a b", "cd"), is("a bcd"));
    // The code generator will ensure that nulls are never passed in. If we
    // pass in null, it is treated like the string "null", as the following
    // tests show. Not the desired behavior for SQL.
    assertThat(concat("a", null), is("anull"));
    assertThat(concat((String) null, null), is("nullnull"));
    assertThat(concat(null, "b"), is("nullb"));
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-6433">[CALCITE-6433]
   * SUBSTRING can return incorrect empty result for some parameters</a>. */
  @Test void testSubString() {
    // str vs single param
    assertThat(substring("string", -1), is("string"));
    assertThat(substring("string", -1L), is("string"));
    assertThat(substring("string", 2), is("tring"));
    assertThat(substring("string", 2L), is("tring"));
    assertThat(substring("string", Integer.MIN_VALUE), is("string"));
    assertThat(substring("string", Long.MIN_VALUE), is("string"));
    assertThat(substring("string", Integer.MIN_VALUE + 10), is("string"));
    assertThat(substring("string", Integer.MAX_VALUE), is(""));
    assertThat(substring("string", Long.MAX_VALUE), is(""));
    assertThat(substring("string", Integer.MAX_VALUE - 10), is(""));
    assertThat(substring("string", Integer.MIN_VALUE - 10L), is("string"));
    assertThat(substring("string", Integer.MAX_VALUE + 10L), is(""));

    // str vs multi params
    assertThat(substring("string", -1, 1), is(""));
    assertThat(substring("string", -1, 1L), is(""));
    assertThat(substring("string", -1L, 1), is(""));
    assertThat(substring("string", -1L, 1L), is(""));

    assertThat(substring("string", 1, 2), is("st"));
    assertThat(substring("string", 1, 2L), is("st"));
    assertThat(substring("string", 1L, 2), is("st"));
    assertThat(substring("string", 1L, 2L), is("st"));

    assertThat(substring("string", -1, 2), is(""));
    assertThat(substring("string", -1L, 2), is(""));
    assertThat(substring("string", -1, 2L), is(""));
    assertThat(substring("string", -1L, 2L), is(""));

    assertThat(substring("string", -1, 3), is("s"));
    assertThat(substring("string", -1L, 3), is("s"));
    assertThat(substring("string", -1, 3L), is("s"));
    assertThat(substring("string", -1L, 3L), is("s"));

    assertThat(substring("string", -10, 12), is("s"));
    assertThat(substring("string", -10L, 12), is("s"));
    assertThat(substring("string", -10, 12L), is("s"));
    assertThat(substring("string", -10L, 12L), is("s"));

    assertThat(substring("string", -1, Integer.MAX_VALUE), is("string"));
    assertThat(substring("string", -1L, Integer.MAX_VALUE), is("string"));
    assertThat(substring("string", -1, Long.MAX_VALUE), is("string"));

    assertThat(substring("string", Integer.MIN_VALUE, Integer.MAX_VALUE), is(""));
    assertThat(substring("string", Integer.MIN_VALUE, Integer.MAX_VALUE + 10L), is("string"));
    assertThat(substring("string", Long.MIN_VALUE, Integer.MAX_VALUE), is(""));
    assertThat(substring("string", Integer.MIN_VALUE, Long.MAX_VALUE), is("string"));
    assertThat(substring("string", Integer.MIN_VALUE - 10L, Long.MAX_VALUE), is("string"));
  }

  @Test void testConcatWithNull() {
    assertThat(concatWithNull("a b", "cd"), is("a bcd"));
    // Null value could be passed in. If we pass one null value,
    // it is treated like empty string, if both values are null, returns null.
    // As the following tests show.
    assertThat(concatWithNull("a", null), is("a"));
    assertThat(concatWithNull(null, null), is(nullValue()));
    assertThat(concatWithNull(null, "b"), is("b"));
  }

  @Test void testConcatMulti() {
    assertThat(concatMulti("a b", "cd", "e"), is("a bcde"));
    // The code generator will ensure that nulls are never passed in. If we
    // pass in null, it is treated like the string "null", as the following
    // tests show. Not the desired behavior for SQL.
    assertThat(concatMulti((String) null), is("null"));
    assertThat(concatMulti((String) null, null), is("nullnull"));
    assertThat(concatMulti("a", null, "b"), is("anullb"));
  }

  @Test void testConcatMultiWithNull() {
    assertThat(concatMultiWithNull("a b", "cd", "e"), is("a bcde"));
    // Null value could be passed in which is treated as empty string
    assertThat(concatMultiWithNull((String) null), is(""));
    assertThat(concatMultiWithNull((String) null, ""), is(""));
    assertThat(concatMultiWithNull((String) null, null, null), is(""));
    assertThat(concatMultiWithNull("a", null, "b"), is("ab"));
  }

  @Test void testConcatMultiWithSeparator() {
    assertThat(concatMultiWithSeparator(",", "a"), is("a"));
    assertThat(concatMultiWithSeparator(",", "a b", "cd"), is("a b,cd"));
    assertThat(concatMultiWithSeparator(",", "a b", null, "cd", null, "e"), is("a b,cd,e"));
    assertThat(concatMultiWithSeparator(",", null, null), is(""));
    assertThat(concatMultiWithSeparator(",", "", ""), is(","));
    assertThat(concatMultiWithSeparator("", "a", "b", null, "c"), is("abc"));
    assertThat(concatMultiWithSeparator("", null, null), is(""));
    // The separator could be null, and it is treated as empty string
    assertThat(concatMultiWithSeparator(null, "a", "b", null, "c"), is("abc"));
    assertThat(concatMultiWithSeparator(null, null, null), is(""));
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-6446">[CALCITE-6446]
   * Add CONCAT_WS function (enabled in Spark library)</a>. */
  @Test void testConcatMultiTypeWithSeparator() {
    // string type
    assertThat(concatMultiTypeWithSeparator("a"), is(""));
    assertThat(concatMultiTypeWithSeparator(",", "a"), is("a"));
    assertThat(concatMultiTypeWithSeparator(",", "a b", "cd"), is("a b,cd"));
    assertThat(concatMultiTypeWithSeparator(",", "a b", null, "cd", null, "e"), is("a b,cd,e"));
    assertThat(concatMultiTypeWithSeparator(",", "", ""), is(","));
    assertThat(concatMultiTypeWithSeparator("", null, null), is(""));
    // array type
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList()), is(""));
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a")), is("a"));
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a", "b")), is("a,b"));
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a", null, "b")), is("a,b"));
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList(null, "b")), is("b"));
    assertThat(concatMultiTypeWithSeparator(",", Arrays.asList(null, null)), is(""));
    assertThat(
        concatMultiTypeWithSeparator(",",
            Arrays.asList("11", "11"), Arrays.asList("12", "12")), is("11,11,12,12"));
    // multi type
    assertThat(concatMultiTypeWithSeparator(",", "11", "11", Arrays.asList("12", "12")),
        is("11,11,12,12"));
    assertThat(concatMultiTypeWithSeparator(",", null, "11", Arrays.asList("12", "12")),
        is("11,12,12"));
    assertThat(concatMultiTypeWithSeparator(",", "11", null, Arrays.asList("12", "12")),
        is("11,12,12"));
    assertThat(
        concatMultiTypeWithSeparator(",", "11", "11", Arrays.asList("12", "12"),
            Arrays.asList("13", null, "13")),
        is("11,11,12,12,13,13"));
  }

  /** Test case for
   * <a href="https://issues.apache.org/jira/browse/CALCITE-6450">[CALCITE-6450]
   * Postgres CONCAT_WS function </a>. */
  @Test void testConcatMultiObjectWithSeparator() {
    assertThat(concatMultiObjectWithSeparator("a"), is(""));
    assertThat(concatMultiObjectWithSeparator(",", "a b", "cd"), is("a b,cd"));
    assertThat(concatMultiObjectWithSeparator(",", "a", 1, Arrays.asList("b", "c")),
        is("a,1,[b, c]"));
    assertThat(concatMultiObjectWithSeparator(",", "a", 1, Arrays.asList("b", "c"), null),
        is("a,1,[b, c]"));
    assertThat(concatMultiObjectWithSeparator("abc", null, null), is(""));
  }

  @Test void testConvertOracle() {
    assertThat(convertOracle("a", "UTF8", "LATIN1"), is("a"));
    assertThat(convertOracle("a", "UTF8"), is("a"));
  }

  @Test void testPosixRegex() {
    final SqlFunctions.PosixRegexFunction f =
        new SqlFunctions.PosixRegexFunction();
    assertThat(f.posixRegexSensitive("abc", "abc"), is(true));
    assertThat(f.posixRegexSensitive("abc", "^a"), is(true));
    assertThat(f.posixRegexSensitive("abc", "(b|d)"), is(true));
    assertThat(f.posixRegexSensitive("abc", "^(b|c)"), is(false));

    assertThat(f.posixRegexInsensitive("abc", "ABC"), is(true));
    assertThat(f.posixRegexInsensitive("abc", "^A"), is(true));
    assertThat(f.posixRegexInsensitive("abc", "(B|D)"), is(true));
    assertThat(f.posixRegexInsensitive("abc", "^(B|C)"), is(false));

    assertThat(f.posixRegexInsensitive("abc", "^[[:xdigit:]]$"), is(false));
    assertThat(f.posixRegexInsensitive("abc", "^[[:xdigit:]]+$"), is(true));
    assertThat(f.posixRegexInsensitive("abcq", "^[[:xdigit:]]+$"), is(false));

    assertThat(f.posixRegexInsensitive("abc", "[[:xdigit:]]"), is(true));
    assertThat(f.posixRegexInsensitive("abc", "[[:xdigit:]]+"), is(true));
    assertThat(f.posixRegexInsensitive("abcq", "[[:xdigit:]]"), is(true));
  }

  @Test void testRegexpContains() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();

    // Use same regex; should hit cache
    assertThat(f.regexpContains("abcdef", "abz*"), is(true));
    assertThat(f.regexpContains("zabzz", "abz*"), is(true));
    assertThat(f.regexpContains("zazbbzz", "abz*"), is(false));
    assertThat(f.regexpContains("abcadcabcaecghi", ""), is(true));

    try {
      final boolean b = f.regexpContains("abc def ghi", "(abc");
      fail("expected error, got " + b);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_CONTAINS: 'Unclosed "
              + "group near index 4 (abc'"));
    }

    try {
      final boolean b = f.regexpContains("abc def ghi", "[z-a]");
      fail("expected error, got " + b);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_CONTAINS: 'Illegal "
              + "character range near index 3 [z-a]    ^'"));
    }

    try {
      final boolean b = f.regexpContains("abc def ghi", "{2,1}");
      fail("expected error, got " + b);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_CONTAINS: 'Illegal "
              + "repetition range near index 4 {2,1}     ^'"));
    }
  }

  @Test void testRegexpExtract() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();

    // basic extracts
    assertThat(f.regexpExtract("abcadcabcaecghi", "ac"), nullValue());
    assertThat(f.regexpExtract("abcadcabcaecghi", ""), is(""));
    assertThat(f.regexpExtract("a9cadca5c4aecghi", "a[0-9]c"), is("a9c"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.*c"), is("abcadcabcaec"));

    // capturing group extracts
    assertThat(f.regexpExtract("abcadcabcaecghi", "abc(a.c)"), is("adc"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "abc(a.c)", 4), is("aec"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "abc(a.c)", 1, 2), is("aec"));

    // position-based extracts
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.c", 25), nullValue());
    assertThat(f.regexpExtract("a9cadca5c4aecghi", "a[0-9]c", 1), is("a9c"));
    assertThat(f.regexpExtract("a9cadca5c4aecghi", "a[0-9]c", 6), is("a5c"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.*c", 7), is("abcaec"));

    // occurrence-based extracts
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.c", 1, 3), is("abc"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.c", 2, 3), is("aec"));
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.c", 1, 5), nullValue());
    assertThat(f.regexpExtract("abcadcabcaecghi", "a.+c", 1, 2), nullValue());

    // exceptional scenarios
    try {
      final String s = f.regexpExtract("abc def ghi", "(abc");
      fail("expected error, got " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_EXTRACT: 'Unclosed group near index 4 "
              + "(abc'"));
    }

    try {
      final String s = f.regexpExtract("abcadcabcaecghi", "(abc)ax(a.c)");
      fail("expected error, got " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Multiple capturing groups (count=2) not allowed in regex input for "
              + "REGEXP_EXTRACT"));
    }

    try {
      final String s = f.regexpExtract("abcadcabcaecghi", "a.c", 0);
      fail("expected error, got " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '0' for argument 'position' in REGEXP_EXTRACT"));
    }

    try {
      final String s = f.regexpExtract("abcadcabcaecghi", "a.c", 3, -1);
      fail("expected error, got " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '-1' for argument 'occurrence' in REGEXP_EXTRACT"));
    }

    try {
      final String s = f.regexpExtract("abcadcabcaecghi", "a.c", -4, 4);
      fail("expected error, got " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '-4' for argument 'position' in REGEXP_EXTRACT"));
    }
  }

  @Test void testRegexpExtractAll() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();

    assertThat(f.regexpExtractAll("abcadcabcaecghi", "ac"), is(list()));
    assertThat(f.regexpExtractAll("abcadc", ""), is(list("", "", "", "", "", "", "")));
    assertThat(f.regexpExtractAll("abcadcabcaecghi", "abc(a.c)"), is(list("adc", "aec")));
    assertThat(f.regexpExtractAll("abcadcabcaecghi", "a.c"), is(list("abc", "adc", "abc", "aec")));
    assertThat(f.regexpExtractAll("banana", "ana"), is(list("ana")));
    assertThat(f.regexpExtractAll("abacadaeafa", "a.a"), is(list("aba", "ada", "afa")));
    assertThat(f.regexpExtractAll("abcdefghijklmnop", ".+"), is(list("abcdefghijklmnop")));

    try {
      final List<String> s = f.regexpExtractAll("abc def ghi", "(abc");
      fail("expected error, got array: " + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_EXTRACT_ALL: 'Unclosed group near index 4 "
              + "(abc'"));
    }

    try {
      final List<String> s = f.regexpExtractAll("abcadcabcaecghi", "(abc).(ax).(a.c)");
      fail("expected error, got array:" + s);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Multiple capturing groups (count=3) not allowed in regex input for "
              + "REGEXP_EXTRACT_ALL"));
    }
  }

  @Test void testRegexpInstr() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();

    // basic searches
    assertThat(f.regexpInstr("abcdefghij", "adc"), is(0));
    assertThat(f.regexpInstr("abcdefghij", ""), is(0));
    assertThat(f.regexpInstr("a9ca5c4aechi", "a[0-9]c"), is(1));
    assertThat(f.regexpInstr("abcadcabcaecghi", ".dc"), is(4));

    // capturing group searches
    assertThat(f.regexpInstr("abcadcabcaecghi", "abc(a.c)"), is(4));
    assertThat(f.regexpInstr("abcadcabcaecghi", "abc(a.c)", 4), is(10));
    assertThat(f.regexpInstr("abcadcabcaecghi", "abc(a.c)", 1, 2), is(10));
    assertThat(f.regexpInstr("abcadcabcaecghi", "abc(a.c)", 1, 2, 1), is(13));

    // position-based searches
    assertThat(f.regexpInstr("abcadcabcaecghi", ".ec", 25), is(0));
    assertThat(f.regexpInstr("a9cadca5c4aecghi", "a[0-9]c", 4), is(7));
    assertThat(f.regexpInstr("abcadcabcaecghi", "a.*c", 7), is(7));

    // occurrence-based searches
    assertThat(f.regexpInstr("a9cadca5c4aecghi", "a[0-9]c", 1, 3), is(0));
    assertThat(f.regexpInstr("a9cadca5c4aecghi", "a[0-9]c", 2, 1), is(7));
    assertThat(f.regexpInstr("a9cadca5c4aecghi", "a[0-9]c", 1, 1), is(1));

    // occurrence_position-based searches
    assertThat(f.regexpInstr("a9cadca5c4aecghi", "a[0-9]c", 1, 1, 0), is(1));
    assertThat(f.regexpInstr("abcadcabcaecghi", "abc(a.c)", 7, 1, 1), is(13));
    assertThat(f.regexpInstr("abcadcabcaec", "abc(a.c)", 4, 1, 1), is(13));

    // exceptional scenarios
    try {
      final int idx = f.regexpInstr("abc def ghi", "{4,1}");
      fail("expected error, got " + idx);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid regular expression for REGEXP_INSTR: 'Illegal repetition range near index 4"
              + " {4,1}     ^'"));
    }

    try {
      final int idx = f.regexpInstr("abcadcabcaecghi", "(.)a(.c)");
      fail("expected error, got " + idx);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Multiple capturing groups (count=2) not allowed in regex input for "
              + "REGEXP_INSTR"));
    }

    try {
      final int idx = f.regexpInstr("abcadcabcaecghi", "a.c", 0);
      fail("expected error, got " + idx);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '0' for argument 'position' in REGEXP_INSTR"));
    }

    try {
      final int idx = f.regexpInstr("abcadcabcaecghi", "a.c", 3, -1);
      fail("expected error, got " + idx);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '-1' for argument 'occurrence' in REGEXP_INSTR"));
    }

    try {
      final int idx = f.regexpInstr("abcadcabcaecghi", "a.c", 2, 4, -4);
      fail("expected error, got " + idx);
    } catch (RuntimeException e) {
      assertThat(e.getMessage(),
          is("Invalid integer input '-4' for argument 'occurrence_position' in REGEXP_INSTR"));
    }
  }

  @Test void testReplace() {
    assertThat(replace("", "ciao", "ci", true), is(""));
    assertThat(replace("ciao", "ciao", "", true), is(""));
    assertThat(replace("ciao", "", "ciao", true), is("ciao"));
    assertThat(replace("ci ao", " ", "ciao", true), is("ciciaoao"));
    assertThat(replace("ciAao", "a", "ciao", true), is("ciAciaoo"));
    assertThat(replace("ciAao", "A", "ciao", true), is("ciciaoao"));
    assertThat(replace("ciAao", "a", "ciao", false), is("ciciaociaoo"));
    assertThat(replace("ciAao", "A", "ciao", false), is("ciciaociaoo"));
    assertThat(replace("hello world", "o", "", true), is("hell wrld"));
  }

  @Test void testRegexpReplace() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();
    assertThat(f.regexpReplace("abc", "b"), is("ac"));
    assertThat(f.regexpReplace("a b c", "b", "X"), is("a X c"));
    assertThat(f.regexpReplace("abc def ghi", "[g-z]+", "X"), is("abc def X"));
    assertThat(f.regexpReplace("abc def ghi", "[a-z]+", "X"), is("X X X"));
    assertThat(f.regexpReplace("a b c", "a|b", "X"), is("X X c"));
    assertThat(f.regexpReplace("a b c", "y", "X"), is("a b c"));

    assertThat(f.regexpReplace("100-200", "(\\d+)", "num"), is("num-num"));
    assertThat(f.regexpReplace("100-200", "(\\d+)", "###"), is("###-###"));
    assertThat(f.regexpReplace("100-200", "(-)", "###"), is("100###200"));

    assertThat(f.regexpReplace("abc def ghi", "[a-z]+", "X", 1), is("X X X"));
    assertThat(f.regexpReplace("abc def ghi", "[a-z]+", "X", 2), is("aX X X"));
    assertThat(f.regexpReplace("abc def ghi", "[a-z]+", "X", 1, 3),
        is("abc def X"));
    assertThat(f.regexpReplace("abc def GHI", "[a-z]+", "X", 1, 3, "c"),
        is("abc def GHI"));
    assertThat(f.regexpReplace("abc def GHI", "[a-z]+", "X", 1, 3, "i"),
        is("abc def X"));
    assertThat(f.regexpReplacePg("abc def GHI", "[a-z]+", "X"), is("X def GHI"));
    assertThat(f.regexpReplacePg("abc def GHI", "[a-z]+", "X", "g"),
        is("X X GHI"));
    assertThat(f.regexpReplacePg("ABC def GHI", "[a-z]+", "X", "i"),
        is("X def GHI"));
    assertThat(f.regexpReplacePg("", "[a-z]+", "X", "i"), is(""));
    assertThat(f.regexpReplace("", "[a-z]+", "X", 1, 1, "i"), is(""));


    try {
      f.regexpReplace("abc def ghi", "[a-z]+", "X", 0);
      fail("'regexp_replace' on an invalid pos is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for REGEXP_REPLACE: '0'"));
    }

    try {
      f.regexpReplace("abc def ghi", "[a-z]+", "X", 1, 3, "WWW");
      fail("'regexp_replace' on an invalid matchType is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for REGEXP_REPLACE: 'WWW'"));
    }

    assertThat(f.regexpReplacePg("abc", "a(.*)c", "x\\1x", "i"),
        is("xbx"));
    assertThat(f.regexpReplace("abc", "a(.*)c", "x$1x"),
        is("xbx"));
    assertThat(f.regexpReplace("abc", "a(.*)c", "x\\1x"),
        is("x1x"));
  }

  @Test void testReplaceNonDollarIndexedString() {
    assertThat(SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\4_\\\\2"),
        is("$4_$2"));
    assertThat(SqlFunctions.RegexFunction.replaceNonDollarIndexedString("abc123"),
        is("abc123"));
    assertThat(SqlFunctions.RegexFunction.replaceNonDollarIndexedString("$007"),
        is("\\$007"));
    assertThat(SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\\\\\ \\\\\\\\"),
        is("\\\\ \\\\"));
    assertThat(SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\\\\\$ $\\\\\\\\"),
        is("\\\\\\$ \\$\\\\"));
    try {
      SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\-x");
      fail("'regexp_replace' with invalid replacement pattern is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid replacement pattern for REGEXP_REPLACE: '\\\\-x'"));
    }
    try {
      SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\ \\\\");
      fail("'regexp_replace' with invalid replacement pattern is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid replacement pattern for REGEXP_REPLACE: '\\\\ \\\\'"));
    }
    try {
      SqlFunctions.RegexFunction.replaceNonDollarIndexedString("\\\\a");
      fail("'regexp_replace' with invalid replacement pattern is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid replacement pattern for REGEXP_REPLACE: '\\\\a'"));
    }
  }

  @Test void testRegexpReplaceNonDollarIndexed() {
    final SqlFunctions.RegexFunction f = new SqlFunctions.RegexFunction();
    assertThat(f.regexpReplaceNonDollarIndexed("abascusB", "b", "X"), is("aXascusB"));
    assertThat(f.regexpReplaceNonDollarIndexed("abc01def02ghi", "[a-z]+", "X"), is("X01X02X"));
    assertThat(f.regexpReplaceNonDollarIndexed("a0b1c2d3", "0|2", "X"), is("aXb1cXd3"));

    // Test double-backslash indexing for capturing groups
    assertThat(f.regexpReplaceNonDollarIndexed("abc_defcon", "([a-z])_([a-z])", "\\\\2_\\\\1"),
        is("abd_cefcon"));
    assertThat(f.regexpReplaceNonDollarIndexed("1\\2\\3\\4\\5", "2.(.).4", "\\\\1"),
        is("1\\3\\5"));
    assertThat(f.regexpReplaceNonDollarIndexed("abc16", "b(.*)(\\d)", "\\\\\\\\"),
        is("a\\"));
    assertThat(f.regexpReplaceNonDollarIndexed("qwerty123", "([0-9]+)", "$147"),
        is("qwerty$147"));

    try {
      f.regexpReplaceNonDollarIndexed("abcdefghijabc", "abc(.)", "\\\\-11x");
      fail("'regexp_replace' with invalid replacement pattern is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid replacement pattern for REGEXP_REPLACE: '\\\\-11x'"));
    }
    try {
      f.regexpReplaceNonDollarIndexed("abcdefghijabc", "abc(.)", "\\\11x");
      fail("'regexp_replace' with invalid replacement pattern is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid replacement pattern for REGEXP_REPLACE: '\\\tx'"));
    }
  }

  @Test void testLower() {
    assertThat(lower("A bCd Iijk"), is("a bcd iijk"));
  }

  @Test void testFromBase64() {
    final List<String> expectedList =
        Arrays.asList("", "\0", "0", "a", " ", "\n", "\r\n", "\u03C0",
            "hello\tword");
    for (String expected : expectedList) {
      assertThat(fromBase64(toBase64(expected)),
          is(new ByteString(expected.getBytes(UTF_8))));
    }
    assertThat("546869732069732061207465737420537472696e672e",
        is(fromBase64("VGhpcyB  pcyBh\rIHRlc3Qg\tU3Ry\naW5nLg==").toString()));
    assertThat(fromBase64("-1"), nullValue());
  }

  @Test void testToBase64() {
    final String s = ""
        + "This is a test String. check resulte out of 76This is a test String."
        + "This is a test String.This is a test String.This is a test String."
        + "This is a test String. This is a test String. check resulte out of 76"
        + "This is a test String.This is a test String.This is a test String."
        + "This is a test String.This is a test String. This is a test String. "
        + "check resulte out of 76This is a test String.This is a test String."
        + "This is a test String.This is a test String.This is a test String.";
    final String actual = ""
        + "VGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBjaGVjayByZXN1bHRlIG91dCBvZiA3NlRoaXMgaXMgYSB0\n"
        + "ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRo\n"
        + "aXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuIFRoaXMgaXMgYSB0ZXN0\n"
        + "IFN0cmluZy4gY2hlY2sgcmVzdWx0ZSBvdXQgb2YgNzZUaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhp\n"
        + "cyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0cmluZy5UaGlzIGlzIGEgdGVzdCBT\n"
        + "dHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLiBUaGlzIGlzIGEgdGVzdCBTdHJpbmcuIGNoZWNr\n"
        + "IHJlc3VsdGUgb3V0IG9mIDc2VGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMgYSB0ZXN0IFN0\n"
        + "cmluZy5UaGlzIGlzIGEgdGVzdCBTdHJpbmcuVGhpcyBpcyBhIHRlc3QgU3RyaW5nLlRoaXMgaXMg\n"
        + "YSB0ZXN0IFN0cmluZy4=";
    assertThat(toBase64(s), is(actual));
    assertThat(toBase64(""), is(""));
  }

  @Test void testUpper() {
    assertThat(upper("A bCd iIjk"), is("A BCD IIJK"));
  }

  @Test void testInitcap() {
    assertThat(initcap("aA"), is("Aa"));
    assertThat(initcap("zz"), is("Zz"));
    assertThat(initcap("AZ"), is("Az"));
    assertThat(initcap("tRy a littlE  "), is("Try A Little  "));
    assertThat(initcap("won't it?no"), is("Won'T It?No"));
    assertThat(initcap("1A"), is("1a"));
    assertThat(initcap(" b0123B"), is(" B0123b"));
  }

  @Test void testLesser() {
    assertThat(lesser("a", "bc"), is("a"));
    assertThat(lesser("bc", "ac"), is("ac"));
    assertThat(lesser("a", null), is("a"));
    assertThat(lesser(null, "a"), is("a"));
    assertThat(lesser((String) null, null), nullValue());
  }

  @Test void testGreater() {
    assertThat(greater("a", "bc"), is("bc"));
    assertThat(greater("bc", "ac"), is("bc"));
    assertThat(greater("a", null), is("a"));
    assertThat(greater(null, "a"), is("a"));
    assertThat(greater((String) null, null), nullValue());
  }

  /** Test for {@link SqlFunctions#rtrim}. */
  @Test void testRtrim() {
    assertThat(rtrim(""), is(""));
    assertThat(rtrim("    "), is(""));
    assertThat(rtrim("   x  "), is("   x"));
    assertThat(rtrim("   x "), is("   x"));
    assertThat(rtrim("   x y "), is("   x y"));
    assertThat(rtrim("   x"), is("   x"));
    assertThat(rtrim("x"), is("x"));
  }

  /** Test for {@link SqlFunctions#ltrim}. */
  @Test void testLtrim() {
    assertThat(ltrim(""), is(""));
    assertThat(ltrim("    "), is(""));
    assertThat(ltrim("   x  "), is("x  "));
    assertThat(ltrim("   x "), is("x "));
    assertThat(ltrim("x y "), is("x y "));
    assertThat(ltrim("   x"), is("x"));
    assertThat(ltrim("x"), is("x"));
  }

  /** Test for {@link SqlFunctions#trim}. */
  @Test void testTrim() {
    assertThat(trimSpacesBoth(""), is(""));
    assertThat(trimSpacesBoth("    "), is(""));
    assertThat(trimSpacesBoth("   x  "), is("x"));
    assertThat(trimSpacesBoth("   x "), is("x"));
    assertThat(trimSpacesBoth("   x y "), is("x y"));
    assertThat(trimSpacesBoth("   x"), is("x"));
    assertThat(trimSpacesBoth("x"), is("x"));
  }

  /** Test for {@link SqlFunctions#overlay}. */
  @Test void testOverlay() {
    assertThat(overlay("HelloWorld", "Java", 6), is("HelloJavad"));
    assertThat(overlay("Hello World", "World", 1), is("World World"));
    assertThat(overlay("HelloWorld", "Java", 6, 5), is("HelloJava"));
    assertThat(overlay("HelloWorld", "Java", 6, 0), is("HelloJavaWorld"));
  }


  static String trimSpacesBoth(String s) {
    return trim(true, true, " ", s);
  }

  @Test void testFloor() {
    checkFloor(0, 10, 0);
    checkFloor(27, 10, 20);
    checkFloor(30, 10, 30);
    checkFloor(-30, 10, -30);
    checkFloor(-27, 10, -30);
  }

  private void checkFloor(int x, int y, int result) {
    assertThat(SqlFunctions.floor(x, y), is(result));
    assertThat(SqlFunctions.floor((long) x, (long) y), is((long) result));
    assertThat(SqlFunctions.floor((short) x, (short) y), is((short) result));
    assertThat(SqlFunctions.floor((byte) x, (byte) y), is((byte) result));
    assertThat(
        SqlFunctions.floor(BigDecimal.valueOf(x), BigDecimal.valueOf(y)),
        is(BigDecimal.valueOf(result)));
  }

  @Test void testCeil() {
    checkCeil(0, 10, 0);
    checkCeil(27, 10, 30);
    checkCeil(30, 10, 30);
    checkCeil(-30, 10, -30);
    checkCeil(-27, 10, -20);
    checkCeil(-27, 1, -27);
  }

  private void checkCeil(int x, int y, int result) {
    assertThat(SqlFunctions.ceil(x, y), is(result));
    assertThat(SqlFunctions.ceil((long) x, (long) y), is((long) result));
    assertThat(SqlFunctions.ceil((short) x, (short) y), is((short) result));
    assertThat(SqlFunctions.ceil((byte) x, (byte) y), is((byte) result));
    assertThat(
        SqlFunctions.ceil(BigDecimal.valueOf(x), BigDecimal.valueOf(y)),
        is(BigDecimal.valueOf(result)));
  }

  /** Unit test for
   * {@link Utilities#compare(java.util.List, java.util.List)}. */
  @Test void testCompare() {
    final List<String> ac = Arrays.asList("a", "c");
    final List<String> abc = Arrays.asList("a", "b", "c");
    final List<String> a = Collections.singletonList("a");
    final List<String> empty = Collections.emptyList();
    assertThat(Utilities.compare(ac, ac), is(0));
    assertThat(Utilities.compare(ac, new ArrayList<>(ac)), is(0));
    assertThat(Utilities.compare(a, ac), is(-1));
    assertThat(Utilities.compare(empty, ac), is(-1));
    assertThat(Utilities.compare(ac, a), is(1));
    assertThat(Utilities.compare(ac, abc), is(1));
    assertThat(Utilities.compare(ac, empty), is(1));
    assertThat(Utilities.compare(empty, empty), is(0));
  }

  @Test void testTruncateLong() {
    assertThat(SqlFunctions.truncate(12345L, 1000L), is(12000L));
    assertThat(SqlFunctions.truncate(12000L, 1000L), is(12000L));
    assertThat(SqlFunctions.truncate(12001L, 1000L), is(12000L));
    assertThat(SqlFunctions.truncate(11999L, 1000L), is(11000L));

    assertThat(SqlFunctions.truncate(-12345L, 1000L), is(-13000L));
    assertThat(SqlFunctions.truncate(-12000L, 1000L), is(-12000L));
    assertThat(SqlFunctions.truncate(-12001L, 1000L), is(-13000L));
    assertThat(SqlFunctions.truncate(-11999L, 1000L), is(-12000L));
  }

  @Test void testTruncateInt() {
    assertThat(SqlFunctions.truncate(12345, 1000), is(12000));
    assertThat(SqlFunctions.truncate(12000, 1000), is(12000));
    assertThat(SqlFunctions.truncate(12001, 1000), is(12000));
    assertThat(SqlFunctions.truncate(11999, 1000), is(11000));

    assertThat(SqlFunctions.truncate(-12345, 1000), is(-13000));
    assertThat(SqlFunctions.truncate(-12000, 1000), is(-12000));
    assertThat(SqlFunctions.truncate(-12001, 1000), is(-13000));
    assertThat(SqlFunctions.truncate(-11999, 1000), is(-12000));

    assertThat(SqlFunctions.round(12345, 1000), is(12000));
    assertThat(SqlFunctions.round(12845, 1000), is(13000));
    assertThat(SqlFunctions.round(-12345, 1000), is(-12000));
    assertThat(SqlFunctions.round(-12845, 1000), is(-13000));
  }

  @Test void testSTruncateDouble() {
    assertThat(SqlFunctions.struncate(12.345d, 3), closeTo(12.345d, 0.001));
    assertThat(SqlFunctions.struncate(12.345d, 2), closeTo(12.340d, 0.001));
    assertThat(SqlFunctions.struncate(12.345d, 1), closeTo(12.300d, 0.001));
    assertThat(SqlFunctions.struncate(12.999d, 0), closeTo(12.000d, 0.001));

    assertThat(SqlFunctions.struncate(-12.345d, 3), closeTo(-12.345d, 0.001));
    assertThat(SqlFunctions.struncate(-12.345d, 2), closeTo(-12.340d, 0.001));
    assertThat(SqlFunctions.struncate(-12.345d, 1), closeTo(-12.300d, 0.001));
    assertThat(SqlFunctions.struncate(-12.999d, 0), closeTo(-12.000d, 0.001));

    assertThat(SqlFunctions.struncate(12345d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.struncate(12000d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.struncate(12001d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.struncate(12000d, -4), closeTo(10000d, 0.001));
    assertThat(SqlFunctions.struncate(12000d, -5), closeTo(0d, 0.001));
    assertThat(SqlFunctions.struncate(11999d, -3), closeTo(11000d, 0.001));

    assertThat(SqlFunctions.struncate(-12345d, -3), closeTo(-12000d, 0.001));
    assertThat(SqlFunctions.struncate(-12000d, -3), closeTo(-12000d, 0.001));
    assertThat(SqlFunctions.struncate(-11999d, -3), closeTo(-11000d, 0.001));
    assertThat(SqlFunctions.struncate(-12000d, -4), closeTo(-10000d, 0.001));
    assertThat(SqlFunctions.struncate(-12000d, -5), closeTo(0d, 0.001));
  }

  @Test void testSTruncateLong() {
    assertThat(SqlFunctions.struncate(12345L, -3), is(12000L));
    assertThat(SqlFunctions.struncate(12000L, -3), is(12000L));
    assertThat(SqlFunctions.struncate(12001L, -3), is(12000L));
    assertThat(SqlFunctions.struncate(12000L, -4), is(10000L));
    assertThat(SqlFunctions.struncate(12000L, -5), is(0L));
    assertThat(SqlFunctions.struncate(11999L, -3), is(11000L));

    assertThat(SqlFunctions.struncate(-12345L, -3), is(-12000L));
    assertThat(SqlFunctions.struncate(-12000L, -3), is(-12000L));
    assertThat(SqlFunctions.struncate(-11999L, -3), is(-11000L));
    assertThat(SqlFunctions.struncate(-12000L, -4), is(-10000L));
    assertThat(SqlFunctions.struncate(-12000L, -5), is(0L));
  }

  @Test void testSTruncateInt() {
    assertThat(SqlFunctions.struncate(12345, -3), is(12000));
    assertThat(SqlFunctions.struncate(12000, -3), is(12000));
    assertThat(SqlFunctions.struncate(12001, -3), is(12000));
    assertThat(SqlFunctions.struncate(12000, -4), is(10000));
    assertThat(SqlFunctions.struncate(12000, -5), is(0));
    assertThat(SqlFunctions.struncate(11999, -3), is(11000));

    assertThat(SqlFunctions.struncate(-12345, -3), is(-12000));
    assertThat(SqlFunctions.struncate(-12000, -3), is(-12000));
    assertThat(SqlFunctions.struncate(-11999, -3), is(-11000));
    assertThat(SqlFunctions.struncate(-12000, -4), is(-10000));
    assertThat(SqlFunctions.struncate(-12000, -5), is(0));
  }

  @Test void testSRoundDouble() {
    assertThat(SqlFunctions.sround(12.345d, 3), closeTo(12.345d, 0.001));
    assertThat(SqlFunctions.sround(12.345d, 2), closeTo(12.350d, 0.001));
    assertThat(SqlFunctions.sround(12.345d, 1), closeTo(12.300d, 0.001));
    assertThat(SqlFunctions.sround(12.999d, 2), closeTo(13.000d, 0.001));
    assertThat(SqlFunctions.sround(12.999d, 1), closeTo(13.000d, 0.001));
    assertThat(SqlFunctions.sround(12.999d, 0), closeTo(13.000d, 0.001));

    assertThat(SqlFunctions.sround(-12.345d, 3), closeTo(-12.345d, 0.001));
    assertThat(SqlFunctions.sround(-12.345d, 2), closeTo(-12.350d, 0.001));
    assertThat(SqlFunctions.sround(-12.345d, 1), closeTo(-12.300d, 0.001));
    assertThat(SqlFunctions.sround(-12.999d, 2), closeTo(-13.000d, 0.001));
    assertThat(SqlFunctions.sround(-12.999d, 1), closeTo(-13.000d, 0.001));
    assertThat(SqlFunctions.sround(-12.999d, 0), closeTo(-13.000d, 0.001));

    assertThat(SqlFunctions.sround(12345d, -1), closeTo(12350d, 0.001));
    assertThat(SqlFunctions.sround(12345d, -2), closeTo(12300d, 0.001));
    assertThat(SqlFunctions.sround(12345d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.sround(12000d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.sround(12001d, -3), closeTo(12000d, 0.001));
    assertThat(SqlFunctions.sround(12000d, -4), closeTo(10000d, 0.001));
    assertThat(SqlFunctions.sround(12000d, -5), closeTo(0d, 0.001));
    assertThat(SqlFunctions.sround(11999d, -3), closeTo(12000d, 0.001));

    assertThat(SqlFunctions.sround(-12345d, -1), closeTo(-12350d, 0.001));
    assertThat(SqlFunctions.sround(-12345d, -2), closeTo(-12300d, 0.001));
    assertThat(SqlFunctions.sround(-12345d, -3), closeTo(-12000d, 0.001));
    assertThat(SqlFunctions.sround(-12000d, -3), closeTo(-12000d, 0.001));
    assertThat(SqlFunctions.sround(-11999d, -3), closeTo(-12000d, 0.001));
    assertThat(SqlFunctions.sround(-12000d, -4), closeTo(-10000d, 0.001));
    assertThat(SqlFunctions.sround(-12000d, -5), closeTo(0d, 0.001));
  }

  @Test void testSRoundLong() {
    assertThat(SqlFunctions.sround(12345L, -1), is(12350L));
    assertThat(SqlFunctions.sround(12345L, -2), is(12300L));
    assertThat(SqlFunctions.sround(12345L, -3), is(12000L));
    assertThat(SqlFunctions.sround(12000L, -3), is(12000L));
    assertThat(SqlFunctions.sround(12001L, -3), is(12000L));
    assertThat(SqlFunctions.sround(12000L, -4), is(10000L));
    assertThat(SqlFunctions.sround(12000L, -5), is(0L));
    assertThat(SqlFunctions.sround(11999L, -3), is(12000L));

    assertThat(SqlFunctions.sround(-12345L, -1), is(-12350L));
    assertThat(SqlFunctions.sround(-12345L, -2), is(-12300L));
    assertThat(SqlFunctions.sround(-12345L, -3), is(-12000L));
    assertThat(SqlFunctions.sround(-12000L, -3), is(-12000L));
    assertThat(SqlFunctions.sround(-11999L, -3), is(-12000L));
    assertThat(SqlFunctions.sround(-12000L, -4), is(-10000L));
    assertThat(SqlFunctions.sround(-12000L, -5), is(0L));
  }

  @Test void testSRoundInt() {
    assertThat(SqlFunctions.sround(12345, -1), is(12350));
    assertThat(SqlFunctions.sround(12345, -2), is(12300));
    assertThat(SqlFunctions.sround(12345, -3), is(12000));
    assertThat(SqlFunctions.sround(12000, -3), is(12000));
    assertThat(SqlFunctions.sround(12001, -3), is(12000));
    assertThat(SqlFunctions.sround(12000, -4), is(10000));
    assertThat(SqlFunctions.sround(12000, -5), is(0));
    assertThat(SqlFunctions.sround(11999, -3), is(12000));

    assertThat(SqlFunctions.sround(-12345, -1), is(-12350));
    assertThat(SqlFunctions.sround(-12345, -2), is(-12300));
    assertThat(SqlFunctions.sround(-12345, -3), is(-12000));
    assertThat(SqlFunctions.sround(-12000, -3), is(-12000));
    assertThat(SqlFunctions.sround(-11999, -3), is(-12000));
    assertThat(SqlFunctions.sround(-12000, -4), is(-10000));
    assertThat(SqlFunctions.sround(-12000, -5), is(0));
  }

  @Test void testSplit() {
    assertThat("no occurrence of delimiter",
        SqlFunctions.split("abc", ","), is(list("abc")));
    assertThat("delimiter in middle",
        SqlFunctions.split("abc", "b"), is(list("a", "c")));
    assertThat("delimiter at end",
        SqlFunctions.split("abc", "c"), is(list("ab", "")));
    assertThat("delimiter at start",
        SqlFunctions.split("abc", "a"), is(list("", "bc")));
    assertThat("empty delimiter",
        SqlFunctions.split("abc", ""), is(list("abc")));
    assertThat("empty delimiter and string",
        SqlFunctions.split("", ""), is(list()));
    assertThat("empty string",
        SqlFunctions.split("", ","), is(list()));
    assertThat("long delimiter (occurs at start)",
        SqlFunctions.split("abracadabra", "ab"), is(list("", "racad", "ra")));
    assertThat("long delimiter (occurs at end)",
        SqlFunctions.split("sabracadabrab", "ab"),
        is(list("s", "racad", "r", "")));

    // Same as above but for ByteString
    final ByteString a = ByteString.of("aa", 16);
    final ByteString ab = ByteString.of("aabb", 16);
    final ByteString abc = ByteString.of("aabbcc", 16);
    final ByteString abracadabra = ByteString.of("aabb44aaccaaddaabb44aa", 16);
    final ByteString b = ByteString.of("bb", 16);
    final ByteString bc = ByteString.of("bbcc", 16);
    final ByteString c = ByteString.of("cc", 16);
    final ByteString f = ByteString.of("ff", 16);
    final ByteString r = ByteString.of("44", 16);
    final ByteString ra = ByteString.of("44aa", 16);
    final ByteString racad = ByteString.of("44aaccaadd", 16);
    final ByteString empty = ByteString.of("", 16);
    final ByteString s = ByteString.of("55", 16);
    final ByteString sabracadabrab =
        ByteString.of("55", 16).concat(abracadabra).concat(b);
    assertThat("no occurrence of delimiter",
        SqlFunctions.split(abc, f), is(list(abc)));
    assertThat("delimiter in middle",
        SqlFunctions.split(abc, b), is(list(a, c)));
    assertThat("delimiter at end",
        SqlFunctions.split(abc, c), is(list(ab, empty)));
    assertThat("delimiter at start",
        SqlFunctions.split(abc, a), is(list(empty, bc)));
    assertThat("empty delimiter",
        SqlFunctions.split(abc, empty), is(list(abc)));
    assertThat("empty delimiter and string",
        SqlFunctions.split(empty, empty), is(list()));
    assertThat("empty string",
        SqlFunctions.split(empty, f), is(list()));
    assertThat("long delimiter (occurs at start)",
        SqlFunctions.split(abracadabra, ab), is(list(empty, racad, ra)));
    assertThat("long delimiter (occurs at end)",
        SqlFunctions.split(sabracadabrab, ab),
        is(list(s, racad, r, empty)));
  }

  @Test void testSplitPart() {
    assertThat(SqlFunctions.splitPart("abc~@~def~@~ghi", "~@~", 2), is("def"));
    assertThat(SqlFunctions.splitPart("abc,def,ghi,jkl", ",", -2), is("ghi"));

    assertThat(SqlFunctions.splitPart("abc,,ghi", ",", 2), is(""));
    assertThat(SqlFunctions.splitPart("", ",", 1), is(""));
    assertThat(SqlFunctions.splitPart("abc", "", 1), is(""));

    assertThat(SqlFunctions.splitPart(null, ",", 1), is(""));
    assertThat(SqlFunctions.splitPart("abc,def", null, 1), is(""));
    assertThat(SqlFunctions.splitPart("abc,def", ",", 0), is(""));

    assertThat(SqlFunctions.splitPart("abc,def", ",", 3), is(""));
    assertThat(SqlFunctions.splitPart("abc,def", ",", -3), is(""));
  }

  @Test void testByteString() {
    final byte[] bytes = {(byte) 0xAB, (byte) 0xFF};
    final ByteString byteString = new ByteString(bytes);
    assertThat(byteString.length(), is(2));
    assertThat(byteString, hasToString("abff"));
    assertThat(byteString.toString(16), is("abff"));
    assertThat(byteString.toString(2), is("1010101111111111"));

    final ByteString emptyByteString = new ByteString(new byte[0]);
    assertThat(emptyByteString.length(), is(0));
    assertThat(emptyByteString, hasToString(""));
    assertThat(emptyByteString.toString(16), is(""));
    assertThat(emptyByteString.toString(2), is(""));

    assertThat(ByteString.EMPTY, is(emptyByteString));

    assertThat(byteString.substring(1, 2), hasToString("ff"));
    assertThat(byteString.substring(0, 2), hasToString("abff"));
    assertThat(byteString.substring(2, 2), hasToString(""));

    // Add empty string, get original string back
    assertSame(byteString.concat(emptyByteString), byteString);
    final ByteString byteString1 = new ByteString(new byte[]{(byte) 12});
    assertThat(byteString.concat(byteString1), hasToString("abff0c"));

    final byte[] bytes3 = {(byte) 0xFF};
    final ByteString byteString3 = new ByteString(bytes3);

    assertThat(byteString.indexOf(emptyByteString), is(0));
    assertThat(byteString.indexOf(byteString1), is(-1));
    assertThat(byteString.indexOf(byteString3), is(1));
    assertThat(byteString3.indexOf(byteString), is(-1));

    thereAndBack(bytes);
    thereAndBack(emptyByteString.getBytes());
    thereAndBack(new byte[]{10, 0, 29, -80});

    assertThat(ByteString.of("ab12", 16).toString(16), equalTo("ab12"));
    assertThat(ByteString.of("AB0001DdeAD3", 16).toString(16),
        equalTo("ab0001ddead3"));
    assertThat(ByteString.of("", 16), equalTo(emptyByteString));
    try {
      ByteString x = ByteString.of("ABg0", 16);
      fail("expected error, got " + x);
    } catch (IllegalArgumentException e) {
      assertThat(e.getMessage(), equalTo("invalid hex character: g"));
    }
    try {
      ByteString x = ByteString.of("ABC", 16);
      fail("expected error, got " + x);
    } catch (IllegalArgumentException e) {
      assertThat(e.getMessage(), equalTo("hex string has odd length"));
    }

    final byte[] bytes4 = {10, 0, 1, -80};
    final ByteString byteString4 = new ByteString(bytes4);
    final byte[] bytes5 = {10, 0, 1, 127};
    final ByteString byteString5 = new ByteString(bytes5);
    final ByteString byteString6 = new ByteString(bytes4);

    assertThat(byteString4.compareTo(byteString5) > 0, is(true));
    assertThat(byteString4.compareTo(byteString6) == 0, is(true));
    assertThat(byteString5.compareTo(byteString4) < 0, is(true));
  }

  private void thereAndBack(byte[] bytes) {
    final ByteString byteString = new ByteString(bytes);
    final byte[] bytes2 = byteString.getBytes();
    assertThat(bytes, equalTo(bytes2));

    final String base64String = byteString.toBase64String();
    final ByteString byteString1 = ByteString.ofBase64(base64String);
    assertThat(byteString, equalTo(byteString1));
  }

  @Test void testEqWithAny() {
    // Non-numeric same type equality check
    assertThat(SqlFunctions.eqAny("hello", "hello"), is(true));

    // Numeric types equality check
    assertThat(SqlFunctions.eqAny(1, 1L), is(true));
    assertThat(SqlFunctions.eqAny(1, 1.0D), is(true));
    assertThat(SqlFunctions.eqAny(1L, 1.0D), is(true));
    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1), is(true));
    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1L), is(true));
    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), 1.0D), is(true));
    assertThat(SqlFunctions.eqAny(new BigDecimal(1L), new BigDecimal(1.0D)),
        is(true));

    // Non-numeric different type equality check
    assertThat(SqlFunctions.eqAny("2", 2), is(false));
  }

  @Test void testNeWithAny() {
    // Non-numeric same type inequality check
    assertThat(SqlFunctions.neAny("hello", "world"), is(true));

    // Numeric types inequality check
    assertThat(SqlFunctions.neAny(1, 2L), is(true));
    assertThat(SqlFunctions.neAny(1, 2.0D), is(true));
    assertThat(SqlFunctions.neAny(1L, 2.0D), is(true));
    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1), is(true));
    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1L), is(true));
    assertThat(SqlFunctions.neAny(new BigDecimal(2L), 1.0D), is(true));
    assertThat(SqlFunctions.neAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is(true));

    // Non-numeric different type inequality check
    assertThat(SqlFunctions.neAny("2", 2), is(true));
  }

  @Test void testLtWithAny() {
    // Non-numeric same type "less then" check
    assertThat(SqlFunctions.ltAny("apple", "banana"), is(true));

    // Numeric types "less than" check
    assertThat(SqlFunctions.ltAny(1, 2L), is(true));
    assertThat(SqlFunctions.ltAny(1, 2.0D), is(true));
    assertThat(SqlFunctions.ltAny(1L, 2.0D), is(true));
    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2), is(true));
    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2L), is(true));
    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), 2.0D), is(true));
    assertThat(SqlFunctions.ltAny(new BigDecimal(1L), new BigDecimal(2.0D)),
        is(true));

    // Non-numeric different type but both implements Comparable
    // "less than" check
    try {
      assertThat(SqlFunctions.ltAny("1", 2L), is(false));
      fail("'lt' on non-numeric different type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for comparison: class java.lang.String < "
              + "class java.lang.Long"));
    }
  }

  @Test void testLeWithAny() {
    // Non-numeric same type "less or equal" check
    assertThat(SqlFunctions.leAny("apple", "banana"), is(true));
    assertThat(SqlFunctions.leAny("apple", "apple"), is(true));

    // Numeric types "less or equal" check
    assertThat(SqlFunctions.leAny(1, 2L), is(true));
    assertThat(SqlFunctions.leAny(1, 1L), is(true));
    assertThat(SqlFunctions.leAny(1, 2.0D), is(true));
    assertThat(SqlFunctions.leAny(1, 1.0D), is(true));
    assertThat(SqlFunctions.leAny(1L, 2.0D), is(true));
    assertThat(SqlFunctions.leAny(1L, 1.0D), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2L), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1L), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 2.0D), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), 1.0D), is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), new BigDecimal(2.0D)),
        is(true));
    assertThat(SqlFunctions.leAny(new BigDecimal(1L), new BigDecimal(1.0D)),
        is(true));

    // Non-numeric different type but both implements Comparable
    // "less or equal" check
    try {
      assertThat(SqlFunctions.leAny("2", 2L), is(false));
      fail("'le' on non-numeric different type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for comparison: class java.lang.String <= "
              + "class java.lang.Long"));
    }
  }

  @Test void testGtWithAny() {
    // Non-numeric same type "greater then" check
    assertThat(SqlFunctions.gtAny("banana", "apple"), is(true));

    // Numeric types "greater than" check
    assertThat(SqlFunctions.gtAny(2, 1L), is(true));
    assertThat(SqlFunctions.gtAny(2, 1.0D), is(true));
    assertThat(SqlFunctions.gtAny(2L, 1.0D), is(true));
    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1), is(true));
    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1L), is(true));
    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), 1.0D), is(true));
    assertThat(SqlFunctions.gtAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is(true));

    // Non-numeric different type but both implements Comparable
    // "greater than" check
    try {
      assertThat(SqlFunctions.gtAny("2", 1L), is(false));
      fail("'gt' on non-numeric different type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for comparison: class java.lang.String > "
              + "class java.lang.Long"));
    }
  }

  @Test void testGeWithAny() {
    // Non-numeric same type "greater or equal" check
    assertThat(SqlFunctions.geAny("banana", "apple"), is(true));
    assertThat(SqlFunctions.geAny("apple", "apple"), is(true));

    // Numeric types "greater or equal" check
    assertThat(SqlFunctions.geAny(2, 1L), is(true));
    assertThat(SqlFunctions.geAny(1, 1L), is(true));
    assertThat(SqlFunctions.geAny(2, 1.0D), is(true));
    assertThat(SqlFunctions.geAny(1, 1.0D), is(true));
    assertThat(SqlFunctions.geAny(2L, 1.0D), is(true));
    assertThat(SqlFunctions.geAny(1L, 1.0D), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1L), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1L), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(2L), 1.0D), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(1L), 1.0D), is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is(true));
    assertThat(SqlFunctions.geAny(new BigDecimal(1L), new BigDecimal(1.0D)),
        is(true));

    // Non-numeric different type but both implements Comparable
    // "greater or equal" check
    try {
      assertThat(SqlFunctions.geAny("2", 2L), is(false));
      fail("'ge' on non-numeric different type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for comparison: class java.lang.String >= "
              + "class java.lang.Long"));
    }
  }

  @Test void testPlusAny() {
    // null parameters
    assertThat(SqlFunctions.plusAny(null, null), nullValue());
    assertThat(SqlFunctions.plusAny(null, 1), nullValue());
    assertThat(SqlFunctions.plusAny(1, null), nullValue());

    // Numeric types
    assertThat(SqlFunctions.plusAny(2, 1L), is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(2, 1.0D), is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(2L, 1.0D), is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1),
        is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1L),
        is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), 1.0D),
        is((Object) new BigDecimal(3)));
    assertThat(SqlFunctions.plusAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is((Object) new BigDecimal(3)));

    // Non-numeric type
    try {
      SqlFunctions.plusAny("2", 2L);
      fail("'plus' on non-numeric type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for arithmetic: class java.lang.String + "
              + "class java.lang.Long"));
    }
  }

  @Test void testMinusAny() {
    // null parameters
    assertThat(SqlFunctions.minusAny(null, null), nullValue());
    assertThat(SqlFunctions.minusAny(null, 1), nullValue());
    assertThat(SqlFunctions.minusAny(1, null), nullValue());

    // Numeric types
    assertThat(SqlFunctions.minusAny(2, 1L), is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(2, 1.0D), is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(2L, 1.0D), is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1),
        is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1L),
        is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), 1.0D),
        is((Object) new BigDecimal(1)));
    assertThat(SqlFunctions.minusAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is((Object) new BigDecimal(1)));

    // Non-numeric type
    try {
      SqlFunctions.minusAny("2", 2L);
      fail("'minus' on non-numeric type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for arithmetic: class java.lang.String - "
              + "class java.lang.Long"));
    }
  }

  @Test void testMultiplyAny() {
    // null parameters
    assertThat(SqlFunctions.multiplyAny(null, null), nullValue());
    assertThat(SqlFunctions.multiplyAny(null, 1), nullValue());
    assertThat(SqlFunctions.multiplyAny(1, null), nullValue());

    // Numeric types
    assertThat(SqlFunctions.multiplyAny(2, 1L), is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(2, 1.0D),
        is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(2L, 1.0D),
        is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1),
        is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1L),
        is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), 1.0D),
        is(new BigDecimal(2)));
    assertThat(SqlFunctions.multiplyAny(new BigDecimal(2L), new BigDecimal(1.0D)),
        is(new BigDecimal(2)));

    // Non-numeric type
    try {
      SqlFunctions.multiplyAny("2", 2L);
      fail("'multiply' on non-numeric type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for arithmetic: class java.lang.String * "
              + "class java.lang.Long"));
    }
  }

  @Test void testDivideAny() {
    // null parameters
    assertThat(SqlFunctions.divideAny(null, null), nullValue());
    assertThat(SqlFunctions.divideAny(null, 1), nullValue());
    assertThat(SqlFunctions.divideAny(1, null), nullValue());

    // Numeric types
    assertThat(SqlFunctions.divideAny(5, 2L),
        is(new BigDecimal("2.5")));
    assertThat(SqlFunctions.divideAny(5, 2.0D),
        is(new BigDecimal("2.5")));
    assertThat(SqlFunctions.divideAny(5L, 2.0D),
        is(new BigDecimal("2.5")));
    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2),
        is(new BigDecimal(2.5)));
    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2L),
        is(new BigDecimal(2.5)));
    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), 2.0D),
        is(new BigDecimal(2.5)));
    assertThat(SqlFunctions.divideAny(new BigDecimal(5L), new BigDecimal(2.0D)),
        is(new BigDecimal(2.5)));

    // Non-numeric type
    try {
      SqlFunctions.divideAny("5", 2L);
      fail("'divide' on non-numeric type is not possible");
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid types for arithmetic: class java.lang.String / "
              + "class java.lang.Long"));
    }
  }

  @Test void testMultiset() {
    final List<String> abacee = Arrays.asList("a", "b", "a", "c", "e", "e");
    final List<String> adaa = Arrays.asList("a", "d", "a", "a");
    final List<String> addc = Arrays.asList("a", "d", "c", "d", "c");
    final List<String> z = Collections.emptyList();
    assertThat(SqlFunctions.multisetExceptAll(abacee, addc),
        isListOf("b", "a", "e", "e"));
    assertThat(SqlFunctions.multisetExceptAll(abacee, z), is(abacee));
    assertThat(SqlFunctions.multisetExceptAll(z, z), is(z));
    assertThat(SqlFunctions.multisetExceptAll(z, addc), is(z));

    assertThat(SqlFunctions.multisetExceptDistinct(abacee, addc),
        isListOf("b", "e"));
    assertThat(SqlFunctions.multisetExceptDistinct(abacee, z),
        isListOf("a", "b", "c", "e"));
    assertThat(SqlFunctions.multisetExceptDistinct(z, z), is(z));
    assertThat(SqlFunctions.multisetExceptDistinct(z, addc), is(z));

    Matcher<Object> result;
    result = isListOf("a", "c");
    assertThat(SqlFunctions.multisetIntersectAll(abacee, addc),
        result);
    assertThat(SqlFunctions.multisetIntersectAll(abacee, adaa),
        isListOf("a", "a"));
    assertThat(SqlFunctions.multisetIntersectAll(adaa, abacee),
        isListOf("a", "a"));
    assertThat(SqlFunctions.multisetIntersectAll(abacee, z), is(z));
    assertThat(SqlFunctions.multisetIntersectAll(z, z), is(z));
    assertThat(SqlFunctions.multisetIntersectAll(z, addc), is(z));

    assertThat(SqlFunctions.multisetIntersectDistinct(abacee, addc),
        isListOf("a", "c"));
    assertThat(SqlFunctions.multisetIntersectDistinct(abacee, adaa),
        isListOf("a"));
    assertThat(SqlFunctions.multisetIntersectDistinct(adaa, abacee),
        isListOf("a"));
    assertThat(SqlFunctions.multisetIntersectDistinct(abacee, z), is(z));
    assertThat(SqlFunctions.multisetIntersectDistinct(z, z), is(z));
    assertThat(SqlFunctions.multisetIntersectDistinct(z, addc), is(z));

    assertThat(SqlFunctions.multisetUnionAll(abacee, addc),
        isListOf("a", "b", "a", "c", "e", "e", "a", "d", "c", "d", "c"));
    assertThat(SqlFunctions.multisetUnionAll(abacee, z), is(abacee));
    assertThat(SqlFunctions.multisetUnionAll(z, z), is(z));
    assertThat(SqlFunctions.multisetUnionAll(z, addc), is(addc));

    assertThat(SqlFunctions.multisetUnionDistinct(abacee, addc),
        isListOf("a", "b", "c", "d", "e"));
    assertThat(SqlFunctions.multisetUnionDistinct(abacee, z),
        isListOf("a", "b", "c", "e"));
    assertThat(SqlFunctions.multisetUnionDistinct(z, z), is(z));
    assertThat(SqlFunctions.multisetUnionDistinct(z, addc),
        isListOf("a", "c", "d"));
  }

  @Test void testMd5() {
    assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5("")));
    assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5(ByteString.of("", 16))));
    assertThat("902fbdd2b1df0c4f70b4a5d23525e932", is(md5("ABC")));
    assertThat("902fbdd2b1df0c4f70b4a5d23525e932",
        is(md5(new ByteString("ABC".getBytes(UTF_8)))));
    try {
      String o = md5((String) null);
      fail("Expected NPE, got " + o);
    } catch (NullPointerException e) {
      // ok
    }
  }

  @Test void testSha1() {
    assertThat("da39a3ee5e6b4b0d3255bfef95601890afd80709", is(sha1("")));
    assertThat("da39a3ee5e6b4b0d3255bfef95601890afd80709", is(sha1(ByteString.of("", 16))));
    assertThat("3c01bdbb26f358bab27f267924aa2c9a03fcfdb8", is(sha1("ABC")));
    assertThat("3c01bdbb26f358bab27f267924aa2c9a03fcfdb8",
        is(sha1(new ByteString("ABC".getBytes(UTF_8)))));
    try {
      String o = sha1((String) null);
      fail("Expected NPE, got " + o);
    } catch (NullPointerException e) {
      // ok
    }
  }

  @Test void testSha256() {
    assertThat("e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
        is(sha256("")));
    assertThat("e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855",
        is(sha256(ByteString.of("", 16))));
    assertThat("a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
        is(sha256("Hello World")));
    assertThat("a591a6d40bf420404a011733cfb7b190d62c65bf0bcda32b57b277d9ad9f146e",
        is(sha256(new ByteString("Hello World".getBytes(UTF_8)))));
    try {
      String o = sha256((String) null);
      fail("Expected NPE, got " + o);
    } catch (NullPointerException e) {
      // ok
    }
  }

  @Test void testSha512() {
    assertThat("cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5"
            + "d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
        is(sha512("")));
    assertThat("cf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5"
            + "d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e",
        is(sha512(ByteString.of("", 16))));
    assertThat("2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d858"
            + "5719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
        is(sha512("Hello World")));
    assertThat("2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d858"
            + "5719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b",
        is(sha512(new ByteString("Hello World".getBytes(UTF_8)))));
    try {
      String o = sha512((String) null);
      fail("Expected NPE, got " + o);
    } catch (NullPointerException e) {
      // ok
    }
  }

  @Test void testPosition() {
    assertThat(position("c", "abcdec"), is(3));
    assertThat(position("c", "abcdec", 2), is(3));
    assertThat(position("c", "abcdec", -2), is(3));
    assertThat(position("c", "abcdec", 4), is(6));
    assertThat(position("c", "abcdec", 1, 2), is(6));
    assertThat(position("cde", "abcdecde", -2, 1), is(6));
    assertThat(position("c", "abcdec", -1, 2), is(3));
    assertThat(position("f", "abcdec", 1, 1), is(0));
    assertThat(position("c", "abcdec", 1, 3), is(0));
    try {
      int i = position("c", "abcdec", 0, 1);
      fail("expected error, got: " + i);
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for POSITION function: from operand value must not be zero"));
    }
    try {
      int i = position("c", "abcdec", 1, 0);
      fail("expected error, got: " + i);
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for POSITION function: occurrence operand value must be positive"));
    }
    final ByteString abcdec = ByteString.of("aabbccddeecc", 16);
    final ByteString c = ByteString.of("cc", 16);
    final ByteString dec = ByteString.of("ddeecc", 16);
    final ByteString f = ByteString.of("ff", 16);
    assertThat(position(c, abcdec), is(3));
    assertThat(position(c, abcdec, 2), is(3));
    assertThat(position(c, abcdec, -2), is(3));
    assertThat(position(c, abcdec, 4), is(6));
    assertThat(position(dec, abcdec, -2), is(4));
    assertThat(position(c, abcdec, 1, 2), is(6));
    assertThat(position(c, abcdec, -1, 2), is(3));
    assertThat(position(f, abcdec, 1, 1), is(0));
    assertThat(position(c, abcdec, 1, 3), is(0));
    try {
      int i = position(c, abcdec, 0, 1);
      fail("expected error, got: " + i);
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for POSITION function: from operand value must not be zero"));
    }
    try {
      int i = position(c, abcdec, 1, 0);
      fail("expected error, got: " + i);
    } catch (CalciteException e) {
      assertThat(e.getMessage(),
          is("Invalid input for POSITION function: occurrence operand value must be positive"));
    }
  }


  /**
   * Tests that a date in the local time zone converts to a Unix timestamp in
   * UTC.
   */
  @Test void testToIntWithSqlDate() {
    assertThat(toInt(new java.sql.Date(0L)), is(0));  // rounded to closest day
    assertThat(sqlDate("1970-01-01"), is(0));
    assertThat(sqlDate("1500-04-30"), is(dateStringToUnixDate("1500-04-30")));
  }

  /**
   * Test calendar conversion from the standard Gregorian calendar used by
   * {@code java.sql} and the proleptic Gregorian calendar used by Unix
   * timestamps.
   */
  @Test void testToIntWithSqlDateInGregorianShift() {
    assertThat(sqlDate("1582-10-04"), is(dateStringToUnixDate("1582-10-04")));
    assertThat(sqlDate("1582-10-05"), is(dateStringToUnixDate("1582-10-15")));
    assertThat(sqlDate("1582-10-15"), is(dateStringToUnixDate("1582-10-15")));
  }

  /**
   * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
   *
   * <p>Java may not be able to represent 0001-01-01 depending on the default
   * time zone. If the date would fall outside of Anno Domini (AD) when
   * converted to the default time zone, that date should not be tested.
   *
   * <p>Not every time zone has a January 1st 12:00am, so this test skips those
   * dates.
   */
  @Test void testToIntWithSqlDateInAnsiDateRange() {
    for (int i = 2; i <= 9999; ++i) {
      final String str = String.format(Locale.ROOT, "%04d-01-01", i);
      final java.sql.Date date = java.sql.Date.valueOf(str);
      final Timestamp timestamp = new Timestamp(date.getTime());
      if (timestamp.toString().endsWith("00:00:00.0")) {
        // Test equality if the time is valid in Java
        assertThat("Converts '" + str + "' from SQL to Unix date",
            toInt(date),
            is(dateStringToUnixDate(str)));
      } else {
        // Test result matches legacy behavior if the time cannot be
        // represented in Java. This probably results in a different date but
        // is pretty rare.
        final long expected =
            (date.getTime() + DateTimeUtils.DEFAULT_ZONE.getOffset(date.getTime()))
                / DateTimeUtils.MILLIS_PER_DAY;
        assertThat("Converts '" + str
                + "' from SQL to Unix date using legacy behavior",
            toInt(date),
            is((int) expected));
      }
    }
  }

  /**
   * Test using a custom {@link TimeZone} to calculate the Unix timestamp.
   * Dates created by a {@link java.sql.Date} method should be converted
   * relative to the local time and not UTC.
   */
  @Test public void testToIntWithTimeZone() {
    // Dates created by a Calendar should be converted to a Unix date in that
    // time zone
    final Calendar utcCal =
        Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
    utcCal.set(1970, Calendar.JANUARY, 1, 0, 0, 0);
    utcCal.set(Calendar.MILLISECOND, 0);
    assertThat(
        toInt(new java.sql.Date(utcCal.getTimeInMillis()),
            utcCal.getTimeZone()),
        is(0));

    // Dates should be relative to the local time and not UTC
    final java.sql.Date epoch = java.sql.Date.valueOf("1970-01-01");

    final TimeZone minusDayZone = TimeZone.getDefault();
    minusDayZone.setRawOffset((int) (minusDayZone.getRawOffset() - MILLIS_PER_DAY));
    assertThat(toInt(epoch, minusDayZone), is(-1));

    final TimeZone plusDayZone = TimeZone.getDefault();
    plusDayZone.setRawOffset((int) (plusDayZone.getRawOffset() + MILLIS_PER_DAY));
    assertThat(toInt(epoch, plusDayZone), is(1));
  }

  /**
   * Tests that a nullable date in the local time zone converts to a Unix
   * timestamp in UTC.
   */
  @Test void testToIntOptionalWithLocalTimeZone() {
    assertThat(toIntOptional(java.sql.Date.valueOf("1970-01-01")), is(0));
    assertThat(toIntOptional((java.sql.Date) null), is(nullValue()));
  }

  /**
   * Tests that a nullable date in the given time zone converts to a Unix
   * timestamp in UTC.
   */
  @Test void testToIntOptionalWithCustomTimeZone() {
    final TimeZone utc = TimeZone.getTimeZone("UTC");
    assertThat(toIntOptional(new java.sql.Date(0L), utc), is(0));
    assertThat(toIntOptional(null, utc), is(nullValue()));
  }

  /**
   * Tests that a time in the local time zone converts to a Unix time in UTC.
   */
  @Test void testToIntWithSqlTime() {
    assertThat(sqlTime("00:00:00"), is(timeStringToUnixDate("00:00:00")));
    assertThat(sqlTime("23:59:59"), is(timeStringToUnixDate("23:59:59")));
  }

  /**
   * Tests that a nullable time in the local time zone converts to a Unix time
   * in UTC.
   */
  @Test void testToIntOptionalWithSqlTime() {
    assertThat(toIntOptional(Time.valueOf("00:00:00")), is(0));
    assertThat(toIntOptional((Time) null), is(nullValue()));
  }

  /**
   * Tests that a timestamp in the local time zone converts to a Unix timestamp
   * in UTC.
   */
  @Test void testToLongWithSqlTimestamp() {
    assertThat(sqlTimestamp("1970-01-01 00:00:00"), is(0L));
    assertThat(sqlTimestamp("2014-09-30 15:28:27.356"),
        is(timestampStringToUnixDate("2014-09-30 15:28:27.356")));
    assertThat(sqlTimestamp("1500-04-30 12:00:00.123"),
        is(timestampStringToUnixDate("1500-04-30 12:00:00.123")));
  }

  /**
   * Test using a custom {@link TimeZone} to calculate the Unix timestamp.
   * Timestamps created by a {@link Calendar} should be converted to a Unix
   * timestamp in the given time zone. Timestamps created by a {@link Timestamp}
   * method should be converted relative to the local time and not UTC.
   */
  @Test void testToLongWithSqlTimestampAndCustomTimeZone() {
    final Timestamp epoch = java.sql.Timestamp.valueOf("1970-01-01 00:00:00");

    final Calendar utcCal =
        Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.ROOT);
    utcCal.set(1970, Calendar.JANUARY, 1, 0, 0, 0);
    utcCal.set(Calendar.MILLISECOND, 0);
    assertThat(toLong(new Timestamp(utcCal.getTimeInMillis()), utcCal.getTimeZone()),
        is(0L));

    final TimeZone est = TimeZone.getTimeZone("GMT-5:00");
    assertThat(toLong(epoch, est),
        is(epoch.getTime() + est.getOffset(epoch.getTime())));

    final TimeZone ist = TimeZone.getTimeZone("GMT+5:00");
    assertThat(toLong(epoch, ist),
        is(epoch.getTime() + ist.getOffset(epoch.getTime())));
  }

  /**
   * Test calendar conversion from the standard Gregorian calendar used by
   * {@code java.sql} and the proleptic Gregorian calendar used by Unix
   * timestamps.
   */
  @Test void testToLongWithSqlTimestampInGregorianShift() {
    assertThat(sqlTimestamp("1582-10-04 00:00:00"),
        is(timestampStringToUnixDate("1582-10-04 00:00:00")));
    assertThat(sqlTimestamp("1582-10-05 00:00:00"),
        is(timestampStringToUnixDate("1582-10-15 00:00:00")));
    assertThat(sqlTimestamp("1582-10-15 00:00:00"),
        is(timestampStringToUnixDate("1582-10-15 00:00:00")));
  }

  /**
   * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
   *
   * <p>Java may not be able to represent 0001-01-01 depending on the default
   * time zone. If the date would fall outside of Anno Domini (AD) when
   * converted to the default time zone, that date should not be tested.
   *
   * <p>Not every time zone has a January 1st 12:00am, so this test skips those
   * dates.
   */
  @Test void testToLongWithSqlTimestampInAnsiDateRange() {
    for (int i = 2; i <= 9999; ++i) {
      final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
      final Timestamp timestamp = Timestamp.valueOf(str);
      if (timestamp.toString().endsWith("00:00:00.0")) {
        // Test equality if the time is valid in Java
        assertThat("Converts '" + str + "' from SQL to Unix timestamp",
            toLong(timestamp),
            is(timestampStringToUnixDate(str)));
      } else {
        // Test result matches legacy behavior if the time cannot be represented in Java
        // This probably results in a different date but is pretty rare
        final long expected = timestamp.getTime()
            + DateTimeUtils.DEFAULT_ZONE.getOffset(timestamp.getTime());
        assertThat("Converts '" + str
                + "' from SQL to Unix timestamp using legacy behavior",
            toLong(timestamp),
            is(expected));
      }
    }
  }

  /**
   * Tests that a nullable timestamp in the local time zone converts to a Unix
   * timestamp in UTC.
   */
  @Test void testToLongOptionalWithLocalTimeZone() {
    assertThat(toLongOptional(Timestamp.valueOf("1970-01-01 00:00:00")), is(0L));
    assertThat(toLongOptional(null), is(nullValue()));
  }

  /**
   * Test date after 0001-01-01 required by ANSI SQL - is passed.
   * Test date before 0001-01-01 and malformed date time literal - is failed.
   */
  @Test void testToTimestampWithLocalTimeZone() {
    Long ret = toTimestampWithLocalTimeZone("1970-01-01 00:00:01", TimeZone.getTimeZone("UTC"));
    assertThat(ret, is(1000L));

    ret = toTimestampWithLocalTimeZone("1970-01-01 00:00:01.010", TimeZone.getTimeZone("UTC"));
    assertThat(ret, is(1010L));

    ret = toTimestampWithLocalTimeZone("1970-01-01 00:00:01 "
        + TimeZone.getTimeZone("UTC").getID());
    assertThat(ret, is(1000L));

    // exceptional scenarios
    try {
      ret = toTimestampWithLocalTimeZone("malformed", TimeZone.getDefault());
      fail("expected error, got " + ret);
    } catch (CalciteException e) {
      assertThat(e.getMessage(), containsString("Illegal TIMESTAMP WITH LOCAL TIME ZONE literal"));
    }

    try {
      ret = toTimestampWithLocalTimeZone("0000-01-01 00:00:01", TimeZone.getDefault());
      fail("expected error, got " + ret);
    } catch (CalciteException e) {
      assertThat(e.getMessage(), containsString("Illegal TIMESTAMP WITH LOCAL TIME ZONE literal"));
    }

    try {
      ret = toTimestampWithLocalTimeZone("malformed " + TimeZone.getDefault().getID());
      fail("expected error, got " + ret);
    } catch (CalciteException e) {
      assertThat(e.getMessage(), containsString("Illegal TIMESTAMP WITH LOCAL TIME ZONE literal"));
    }
  }

  /**
   * Tests that a nullable timestamp in the given time zone converts to a Unix
   * timestamp in UTC.
   */
  @Test void testToLongOptionalWithCustomTimeZone() {
    final TimeZone utc = TimeZone.getTimeZone("UTC");
    assertThat(toLongOptional(new Timestamp(0L), utc), is(0L));
    assertThat(toLongOptional(null, utc), is(nullValue()));
  }

  /**
   * Tests that a Unix timestamp converts to a date in the local time zone.
   */
  @Test void testInternalToDate() {
    assertThat(internalToDate(0), is(java.sql.Date.valueOf("1970-01-01")));
    assertThat(internalToDate(dateStringToUnixDate("1500-04-30")),
        is(java.sql.Date.valueOf("1500-04-30")));
  }

  /**
   * Test calendar conversion from the standard Gregorian calendar used by
   * {@code java.sql} and the proleptic Gregorian calendar used by Unix
   * timestamps.
   */
  @Test void testInternalToDateWithGregorianShift() {
    // Gregorian shift
    assertThat(internalToDate(dateStringToUnixDate("1582-10-04")),
        is(java.sql.Date.valueOf("1582-10-04")));
    assertThat(internalToDate(dateStringToUnixDate("1582-10-05")),
        is(java.sql.Date.valueOf("1582-10-15")));
    assertThat(internalToDate(dateStringToUnixDate("1582-10-15")),
        is(java.sql.Date.valueOf("1582-10-15")));
  }

  /**
   * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
   *
   * <p>Java may not be able to represent all dates depending on the default time zone, but both
   * the expected and actual assertion values handles that in the same way.
   */
  @Test void testInternalToDateWithAnsiDateRange() {
    for (int i = 2; i <= 9999; ++i) {
      final String str = String.format(Locale.ROOT, "%04d-01-01", i);
      assertThat(internalToDate(dateStringToUnixDate(str)),
          is(java.sql.Date.valueOf(str)));
    }
  }

  /**
   * Tests that a Unix time converts to a SQL time in the local time zone.
   */
  @Test void testInternalToTime() {
    assertThat(internalToTime(0), is(Time.valueOf("00:00:00")));
    assertThat(internalToTime(86399000), is(Time.valueOf("23:59:59")));
  }

  /**
   * Tests that timestamp can be converted to a string given a custom pattern.
   */
  @Test void testToChar() {
    String pattern1 = "YYYY-MM-DD HH24:MI:SS.MS";
    String pattern2 = "Day, DD HH12:MI:SS";

    final SqlFunctions.DateFormatFunction f =
        new SqlFunctions.DateFormatFunction();
    assertThat(f.toChar(0, pattern1),
        is("1970-01-01 00:00:00.000"));

    assertThat(f.toChar(0, pattern2),
        is("Thursday, 01 12:00:00"));

    final long ts0 = timestampStringToUnixDate("2014-09-30 15:28:27.356");
    assertThat(f.toChar(ts0, pattern1),
        is("2014-09-30 15:28:27.356"));

    assertThat(f.toChar(ts0, pattern2),
        is("Tuesday, 30 03:28:27"));

    final long ts1 = timestampStringToUnixDate("1500-04-30 12:00:00.123");
    assertThat(f.toChar(ts1, pattern1),
        is("1500-04-30 12:00:00.123"));
  }

  @Test void testToDate() {
    String pattern1 = "YYYY-MM-DD";

    final SqlFunctions.DateFormatFunction f =
        new SqlFunctions.DateFormatFunction();

    assertThat(f.toDate("2001-10-06", pattern1), is(11601));
  }

  @Test void testToTimestamp() {
    String pattern1 = "HH24:MI:SS YYYY-MM-DD";

    final SqlFunctions.DateFormatFunction f =
        new SqlFunctions.DateFormatFunction();

    assertThat(f.toTimestamp("18:43:36 2001-10-06", pattern1), is(1002393816000L));
  }

  /**
   * Tests that a Unix timestamp converts to a SQL timestamp in the local time
   * zone.
   */
  @Test void testInternalToTimestamp() {
    assertThat(internalToTimestamp(0),
        is(Timestamp.valueOf("1970-01-01 00:00:00.0")));
    assertThat(internalToTimestamp(timestampStringToUnixDate("2014-09-30 15:28:27.356")),
        is(Timestamp.valueOf("2014-09-30 15:28:27.356")));
    assertThat(internalToTimestamp(timestampStringToUnixDate("1500-04-30 12:00:00.123")),
        is(Timestamp.valueOf("1500-04-30 12:00:00.123")));
  }

  /**
   * Test calendar conversion from the standard Gregorian calendar used by
   * {@code java.sql} and the proleptic Gregorian calendar used by Unix timestamps.
   */
  @Test void testInternalToTimestampWithGregorianShift() {
    assertThat(
        internalToTimestamp(timestampStringToUnixDate("1582-10-04 00:00:00")),
        is(Timestamp.valueOf("1582-10-04 00:00:00.0")));
    assertThat(
        internalToTimestamp(timestampStringToUnixDate("1582-10-05 00:00:00")),
        is(Timestamp.valueOf("1582-10-15 00:00:00.0")));
    assertThat(
        internalToTimestamp(timestampStringToUnixDate("1582-10-15 00:00:00")),
        is(Timestamp.valueOf("1582-10-15 00:00:00.0")));
  }

  /**
   * Test date range 0001-01-01 to 9999-12-31 required by ANSI SQL.
   *
   * <p>Java may not be able to represent all dates depending on the default
   * time zone, but both the expected and actual assertion values handles that
   * in the same way.
   */
  @Test void testInternalToTimestampWithAnsiDateRange() {
    for (int i = 2; i <= 9999; ++i) {
      final String str = String.format(Locale.ROOT, "%04d-01-01 00:00:00", i);
      assertThat(internalToTimestamp(timestampStringToUnixDate(str)),
          is(Timestamp.valueOf(str)));
    }
  }

  private int sqlDate(String str) {
    return toInt(java.sql.Date.valueOf(str));
  }

  private int sqlTime(String str) {
    return toInt(java.sql.Time.valueOf(str));
  }

  private long sqlTimestamp(String str) {
    return toLong(java.sql.Timestamp.valueOf(str));
  }
  @Test void testLeftShift() {
    // Test 1-byte array
    byte[] data1 = {(byte) 0x0F}; // 00001111
    for (int shift = -10; shift <= 10; shift++) {
      byte[] result = SqlFunctions.leftShift(data1.clone(), shift);
      // Just verify it doesn't crash and returns correct length
      assertEquals(1, result.length);
    }

    // Test 2-byte array
    byte[] data2 = {(byte) 0x12, (byte) 0x34};
    for (int shift = -18; shift <= 18; shift++) {
      byte[] result = SqlFunctions.leftShift(data2.clone(), shift);
      assertEquals(2, result.length);
    }

    // Verify specific known cases
    assertArrayEquals(new byte[]{(byte) 0xAB, (byte) 0xCD},
        SqlFunctions.leftShift(new byte[]{(byte) 0xAB, (byte) 0xCD}, 0));

    assertArrayEquals(new byte[]{(byte) 0x80, (byte) 0x00},
        SqlFunctions.leftShift(new byte[]{(byte) 0x40, (byte) 0x00}, 1));
  }
}
